#!/usr/bin/env python
# -*- coding: utf-8 -*-
# SQL type wrappers audited by Joshua Kugler, 2008-02-20
""" This module defines the functions for vehicle unavailable or vu.

"""

from string import Template
from tack.db import query, sql_string, sql_int, sql_date
from tack.lib import string_app, content_headers, status_codes, json_app
from tack.lib.jsontools import json_dumps
from tack.lib.dicttools import query_to_list
from commitment_conflict import set_to_conflicted_using_vehicle
import time

## TODO:  reformat each sql statement for readability   -- Done bfrick
## TODO:  remove sql_statement_ prefixes   -- Done bfrick
## TODO:  replace key variable names with meaningful names and match to regex   -- Done bfrick
## TODO:  replace multiple references to env['form'] with single form      -- Done bfrick
## TODO:  add module and function docstrings       -- Done bfrick
## TODO:  use string.Template instead of string interpolation      -- Done bfrick
## TODO:  replace json_dumps + string_app with json_app      -- Done bfrick
## TODO:  remove query_to_list     -- Done bfrick

select_all_events = """
SELECT vu_id, vehicle_id, vu_start, vu_end, type, description
FROM tmpo_vehicle_unavailable
"""

select_event_data = Template("""
SELECT vu_id, vehicle_id, vu_start, vu_end, type, description
FROM tmpo_vehicle_unavailable
WHERE vu_id = $vu_id
""")

select_event_data_on_vehicle = Template("""
SELECT vu_id, vehicle_id, vu_start, vu_end, type, description
FROM tmpo_vehicle_unavailable
WHERE vehicle_id = $vehicle_id
""")

select_current_vehicles = Template("""
SELECT vu_id, type
FROM tmpo_vehicle_unavailable
WHERE vu_start < getdate() and vu_end > getdate()
""")

insert_event = Template("""
INSERT INTO tmpo_vehicle_unavailable (vehicle_id, type, description, vu_start, vu_end) 
VALUES ( $vehicle_id, $type, '$description', '$start', '$finish' )
""")

update_event_by_id = Template("""
UPDATE tmpo_vehicle_unavailable
SET vehicle_id=$vehicle_id, type=$type, description='$description', 
vu_start='$start', vu_end='$finish'
WHERE vu_id = $vu_id
""")

delete_event_by_id = Template("""
DELETE FROM tmpo_vehicle_unavailable
WHERE vu_id = $vu_id
""")

select_event_data_based_on_date_range = Template("""
SELECT vu_id, vehicle_id, vu_start, vu_end, type, description
FROM tmpo_vehicle_unavailable
WHERE (vu_start <= '$start' AND vu_end >= '$finish') 
OR (vu_start >= '$start' AND vu_start <= '$finish') 
OR (vu_end >= '$start' AND vu_end <= '$finish')
""")


def SETFORM(environ,var):
    """Function SETFORM replaces multiple references to env['form'] with single form."""
    return environ['form'][var]

def ZIPLIST(labels,list):
    """Function ZIPLIST make a dictionary of two variables.  One is a list, the second
    is the labels for the lists.  
    ie; query_to_list([[1,2,3],[4,5,6],[7,8,9]],['one','two','three'])
    [{'three': 3, 'two': 2, 'one': 1}, {'three': 6, 'two': 5, 'one': 4}, 
    {'three': 9, 'two': 8, 'one': 7}] """
    return [dict(zip(labels,i)) for i in list]

def get_all_events(environ, start_response):
    """Function get_all_events selects all rows in table."""
    sql_statement = select_all_events
    rows = query(environ['connection'], sql_statement)
    output = {}
    labels = ['id','vehicle_id','start','end','type','description'] 
    output['events'] = ZIPLIST(labels, rows)
    return json_app(output)(environ, start_response)

def get_event_data(environ, start_response):
    """Function get_event_data selects a row base on id."""   
    sql_statement = select_event_data.substitute(
     vu_id = environ['dispatcher.groups']['vu_id']
    )
    rows = query(environ['connection'], sql_statement)
    output = {}
    labels = ['id','vehicle_id','start','end','type','description'] 
    output['events'] = ZIPLIST(labels, rows)
    return json_app(output)(environ, start_response)

def get_event_data_on_vehicle(environ, start_response):
    """Function get_event_data_on_vehicle selects rows based on a foregin key 
    vehicle id."""   
    sql_statement = select_event_data_on_vehicle.substitute(
     vehicle_id = environ['dispatcher.groups']['vehicle_id']
    )
    rows = query(environ['connection'], sql_statement)
    output = {}
    labels = ['id','vehicle_id','start','end','type','description'] 
    output['events'] = ZIPLIST(labels, rows)
    return json_app(output)(environ, start_response)

def get_current_vehicles(environ, start_response):
    sql_statement = select_current_vehicles
    rows = query(environ['connection'], sql_statement)
    output = {}
    labels = ['id','type'] 
    output['events'] = ZIPLIST(labels, rows)
    return json_app(output)(environ, start_response)

def get_event_data_based_on_date_range(environ, start_response):
    """Function get_event_data_based_on_date_range selects rows from the datbase based
    on start and end date range."""
    try:
        start = environ['dispatcher.groups']['start']
        throwaway = time.strptime(start, '%Y-%m-%d %H:%M')
        finish = environ['dispatcher.groups']['finish']
        throwaway = time.strptime(finish, '%Y-%m-%d %H:%M')
        sql_statement = select_event_data_based_on_date_range.substitute(
        start = start,
        finish = finish
        )
        rows = query(environ['connection'], sql_statement)
        output = {}
        labels = ['id','vehicle_id','start','end','type','description'] 
        output['events'] = ZIPLIST(labels, rows)
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    return json_app(output)(environ, start_response)

def add_event(environ, start_response):
    """Function add_event inserts a new row in the database."""
    try:
        vehicle_id = sql_int(SETFORM(environ,'vehicle_id'))
        description = sql_string(SETFORM(environ,'description'))
        type = sql_int(SETFORM(environ,'type'))
        start = sql_date(SETFORM(environ,'start'), '%Y-%m-%d %H:%M')
        throwaway = time.strptime(start, '%Y-%m-%d %H:%M')
        finish = sql_date(SETFORM(environ,'finish'), '%Y-%m-%d %H:%M')
        throwaway = time.strptime(finish, '%Y-%m-%d %H:%M')
        sql_statement = insert_event.substitute(
          vehicle_id = vehicle_id,
          description = description,
          type = type,
          start = start,
          finish = finish
          )
        query(environ['connection'], sql_statement)
        rows = query(environ['connection'], 'SELECT CAST(@@IDENTITY AS INT)')
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    return json_app(rows)(environ, start_response)

def update_event(environ, start_response):
    """Function update_event updates a row in the database by id."""
    try:
        vu_id = environ['dispatcher.groups']['vu_id']
        vehicle_id = sql_int(SETFORM(environ,'vehicle_id'))
        description = sql_string(SETFORM(environ,'description'))
        type = sql_int(SETFORM(environ,'type'))
        start = sql_date(SETFORM(environ,'start'), '%Y-%m-%d %H:%M')
        throwaway = time.strptime(start, '%Y-%m-%d %H:%M')
        finish = sql_date(SETFORM(environ,'finish'), '%Y-%m-%d %H:%M')
        throwaway = time.strptime(finish, '%Y-%m-%d %H:%M')
        sql_statement = update_event_by_id.substitute(
          vu_id = vu_id,
          vehicle_id = vehicle_id,
          description = description,
          type = type,
          start = start,
          finish = finish
        )
        rows = query(environ['connection'], sql_statement)
        output = 'Event Updated'
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    return json_app(output)(environ, start_response)

def delete_event(environ, start_response):
    """Function delete_event deletes a row by id."""   
    sql_statement = delete_event_by_id.substitute(
     vu_id = environ['dispatcher.groups']['vu_id']
    )
    rows = query(environ['connection'], sql_statement)
    output = 'Event Deleted'
    return json_app(output)(environ, start_response)

def get_affected_schedule_events(environ, vehicle_id, start, finish):
    """Function get_affected_schedule_events selects affected rows in the database."""
    affected_list = []
    sql = get_affected_events_sql % locals()
    rows = query(environ['connection'], sql)
    ## TODO:  make list comprehension
    ## e.g., affected_list = [(row.parent_id or row.id) for row in rows]
    ##for row in rows:
        ##if row.parent_id:
            ##affected_list.append(row.parent_id)
        ##else:
            ##affected_list.append(row.id)
    affected_list = [(row.parent_id or row.id) for row in rows]
    return affected_list
